/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.insurance.cis.controllers;

import com.insurance.cis.connection.CisConnection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


public class EmployeeController {
    public static Vector readProduct() {
        Vector vt = new Vector();
        try {
            String sql = "Select EmployeeID,Username,EmployeeName,Gender,DOB,GroupID from Employee";
            Statement st = CisConnection.getInstance().getSqlConnection().createStatement();
            ResultSet rs = st.executeQuery(sql);

            while (rs.next()) {
                String sql1 = "Select GroupDesc from [Group] where GroupID = ?";
                PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(sql1);
                pst.setInt(1, rs.getInt("GroupID"));
                ResultSet rs1 = pst.executeQuery();
                rs1.next();
                Vector temp = new Vector();
                temp.add(rs.getString("EmployeeID"));
                temp.add(rs.getString("Username"));
                temp.add(rs.getString("EmployeeName"));
                temp.add(rs.getString("Gender"));
                temp.add(rs.getString("DOB"));
                temp.add(rs1.getString("GroupDesc"));
                vt.add(temp);              
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return vt;
        }
    }
    
    public static Vector SearchProduct(String strSearch, int type) {
        Vector vt = new Vector();
        String sql;
        boolean found = false;
        if (type == 1) {
            sql = "select * from Product where ProductID like '%" + strSearch + "%'";
        } else {
            sql = "select * from Product where ProductName like '%" + strSearch + "%'";
        }
        try {
            ResultSet rs = CisConnection.getInstance().getSqlConnection().createStatement().executeQuery(sql);
            while (rs.next()) {
                found = true;
                String sql1 = "Select ProductCatDesc from ProductCategory where ProductCatID = ?";
                PreparedStatement pst = CisConnection.getInstance().getSqlConnection().prepareStatement(sql1);
                pst.setInt(1, rs.getInt("ProductCatID"));
                ResultSet rs1 = pst.executeQuery();
                rs1.next();
                
                Vector temp = new Vector();
                temp.add(rs.getString("ProductID"));
                temp.add(rs.getString("ProductName"));
                temp.add(rs.getString("ProductDesc"));
                temp.add(rs1.getString("ProductCatDesc"));
                vt.add(temp);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return vt;
        }
    }
}
